How to Define a Metric on an Inventory Table
Inventory tables are tables that represent the current inventory, such as the Buildings or Rooms tables. Transaction tables are tables that represent a change to the inventory (such as a Workspace Transaction or a Move Request) or a task (such as a Work Order).
You can define metrics on either inventory tables or transaction tables. Typical inventory metrics are Buildings (number of buildings) or Rentable Area.
When you define a metric on an inventory table, you are taking a snapshot of your inventory at the time the metric rule runs. If the metric recurs every month, the metric rule will take that snapshot every month.
For inventory metrics, the Metric Trend Values table records the history for that metric from the day you start running the metric rule and dates forward. You can only take these snapshots for the time period you are running the metric rule. You cannot obtain data for any period of time before you started running the metrics rule. (As we will see, this isn't the case for metrics on transaction tables, which can summarize data from past transactions, as long as those past transactions exist.)
Example
For example, you can define a Rentable Area metric using the metric definition below. This metric will sum the Rentable Area field of the Buildings (bl) table on the first of every month.
| Field | Value |
|---|---|
| Metric Title | Rentable Area |
| Metric Name | spac_RentableArea |
| Metric Description | Total Rentable Area for all leases and owned properties in the portfolio. |
| Business Implication | This figure gives a sense of the overall scope of the real-estate responsibility from a space perspective. |
| Assumptions | Use the Space Inventory & Performance application to record areas. |
| Collect: Recurrence | Monthly, on the 1st of the Month |
| Collect: From Table | bl
|
| Collect: Formula | field:area_rentable
|
| Report: Aggregate As | SUM |
| Report: Trend Direction | No Significance |
| Numeric Format | Numeric Format Base-Unit Area |
| Decimal Places | 0 Places |
| Metric Granularity Assignments | All, by Building, by Site, by Building Use. |
Comments
Collect: Recurrence. The recurrence frequency determines the time-granularity of the information -- yearly, monthly, etc.. Remember that this is snapshot data: if you later change to a different recurrence (e.g. to change from monthly to weekly), you will invalidate your older data.
Recurrence and Ratios. When defining a recurrence, you may want to consider whether you intend to use the metric as a ratio. For instance, you may wish to calculate costs, such as housekeeping costs, by Rentable Area and calculate your housekeeping costs monthly. To do so, you will want to make Rentable Area a monthly metric as well, since you can only use two metrics in a ratio if they have the same recurrence and the same granularities.
Collect: Table. This instructs the metrics rule to select data from the "bl" or Buildings table.
Collect: Field. This instructs the metrics rule to use the "area_rentable" field as the field to sum.
Report: Aggregate As. By choosing "SUM" we total all the values in the area_rentable field. If we chose "COUNT" we would count the records instead (and calculate the number of buildings). "AVERAGE", "MIN", and "MAX" are the other aggregation operators you can use.
SQL. The definition above forms an SQL statement equivalent to:
SELECT SUM(area_rentable) FROM bl
Numeric Format. The "Base-Unit Area" means that this field is in the base unit of measurement (Square Feet or Square Meters) that the project database is. The metrics controls will convert this stored value to the user-display units of measurement based on the user's locale.
Metric Granularity Assignments. These assignments instruct the metric rule to collect snapshot data for each drill-down category, such as Building, Site, or Building Use.
If you have detailed granularities, the metric rule repeats the statement for each level of granularity. For instance, if you specify the "by Building" granularity, the program executes:
SELECT SUM(area_rentable) FROM bl GROUP BY bl_id ORDER BY bl_id
Deletions. Remember if you delete an inventory metric, you also delete all of your recorded trend data. Since inventory metrics are a snapshot of inventory, the program cannot recreate this data again later. (This differs for metrics on transaction tables, as the program can use the transaction date to recreate data on transactions that happened in the past.)